﻿using MySql.Data.MySqlClient;
using Server.CustomObjects;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Server.DataBase
{
    class DBContacts : Server.Interfaces.IDataBaseContacts
    {
         MySqlConnection mConnection;
        
        public DBContacts(MySqlConnection xConnection)
        {
            this.mConnection = xConnection;
        }

        public Boolean RequestFriendship(int pRequesterID, int pTagetUserID)
         {
             Boolean _Output = false;
             MySqlCommand command = null;
             
            if (pTagetUserID < 1 || pRequesterID < 1) return false;

            if (this.UserIsFriend(pTagetUserID, pRequesterID)) return false;
            if (this.UserAllreadyRequested(pRequesterID, pTagetUserID)) return false;
            
            //Hat der andere bereits einen Request gesendet? --> Exit
            if (this.UserAllreadyRequested(pTagetUserID,pRequesterID)) return false;
            
            try
            {
                command = mConnection.CreateCommand();
                command.CommandText = "INSERT INTO " + Globals.Tables.FRIENDREQUESTS +
                " (" + Globals.ConRequestFields.ID + "," +
                Globals.ConRequestFields.REQUESTER + "," +
                Globals.ConRequestFields.TARGET  +
                ") VALUE (NULL,?RequesterID,?TargetUserID)";

                command.Parameters.Add("@RequesterID", MySqlDbType.Int32).Value =pRequesterID ;
                command.Parameters.Add("@TargetUserID", MySqlDbType.Int32).Value =pTagetUserID ;
                
                if (command.ExecuteNonQuery() == 1) _Output = true;
                 
               
            }
            catch (Exception e)
            {
                _Output = false;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------\n" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {
                command = null;
                
            }
            return _Output;
         }
   
        public Boolean RequestFriendship(CustomObjects.User pRequester, int pTagetUserID)
         {
             return this.RequestFriendship(pRequester.ID, pTagetUserID);
         }

        public Boolean RequestFriendship(CustomObjects.User pRequester, CustomObjects.User pTargetUser)
        {
            return this.RequestFriendship(pRequester.ID, pTargetUser.ID);
        }

        public Boolean AcceptFriendship(int pAccepterID, int pRequesterID)
        {
            Boolean _Output = false;
            MySqlCommand command = null;

            if (pAccepterID < 1 || pRequesterID < 1) return false;

            //If allready a friend --> exit
            if (this.UserIsFriend(pAccepterID, pRequesterID)) return false;               

            //If no request happend --> exit
            //CHECK IF THE REQUESTER realy requested! Otherwise --> Exit!
            if (!this.UserAllreadyRequested(pRequesterID, pAccepterID)) return false;      

            try
            {
                command = mConnection.CreateCommand();
                command.CommandText = "INSERT INTO " + Globals.Tables.CONTACTS +
                                    " (" + Globals.Contacts.ID + "," +
                                        Globals.Contacts.USERA + "," +
                                        Globals.Contacts.USERB +
                                        ") VALUE (NULL,?USERA,?USERB)";

                command.Parameters.Add("@USERA", MySqlDbType.Int32).Value = pAccepterID;
                command.Parameters.Add("@USERB", MySqlDbType.Int32).Value = pRequesterID;
                
                if (command.ExecuteNonQuery() == 1)
                    if (this.RemoveRequest(pRequesterID,pAccepterID)) _Output = true;
            }
            catch (Exception e)
            {
                _Output = false;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------\n" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {  

                command = null;

            }
            return _Output;
        }

        public Boolean AcceptFriendship(CustomObjects.User pAccepter, int pRequesterID)
        {
            return this.AcceptFriendship(pAccepter.ID, pRequesterID);
        }

        public Boolean AcceptFriendship(CustomObjects.User pAccepter, CustomObjects.User pRequester)
        {
            return this.AcceptFriendship(pAccepter.ID, pRequester.ID);
        }

        public Boolean DeclineFriendship(int pDeclinerID, int pRequesterID)
        {
            Boolean _Output = false;
            MySqlCommand command = null;

            if (pDeclinerID < 1 || pRequesterID < 1) return false;

            if (this.UserIsFriend(pDeclinerID, pRequesterID)) return false;
            if (!this.UserAllreadyRequested(pRequesterID, pDeclinerID)) return false;

            try
            {
                command = mConnection.CreateCommand();
                command.CommandText =  " UPDATE " + 
                                        Globals.Tables.FRIENDREQUESTS +
                                    " SET " + 
                                        Globals.ConRequestFields.DECLINED + " = true" +
                                    " WHERE " + 
                                        Globals.ConRequestFields.REQUESTER + " = ?RequesterID " +
                                    " AND " + 
                                        Globals.ConRequestFields.TARGET + " = ?pDeclinerID ";
                                         
                command.Parameters.Add("@RequesterID", MySqlDbType.Int32).Value = pRequesterID;
                command.Parameters.Add("@pDeclinerID", MySqlDbType.Int32).Value = pDeclinerID;
                if (command.ExecuteNonQuery() == 1) _Output = true;


            }
            catch (Exception e)
            {
                _Output = false;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------\n" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {
                command = null;

            }
            return _Output;
        }

        public Boolean DeclineFriendship(CustomObjects.User pDecliner, int pRequesterID)
        {
            return this.DeclineFriendship(pDecliner.ID,pRequesterID);
        }

        public Boolean DeclineFriendship(CustomObjects.User pDecliner, CustomObjects.User pRequester)
        {
        return this.DeclineFriendship(pDecliner.ID,pRequester.ID);
        }

        public Boolean CancelFriendship(int pCancelerID, int pExFriendID)
        {
            Boolean _Output = false;
            MySqlCommand command = null;

            if (pCancelerID < 1 || pExFriendID < 1) return false;
            try
            {
                command = mConnection.CreateCommand();
                command.CommandText = "DELETE FROM " +
                                        Globals.Tables.CONTACTS +
                                    " WHERE " +
                                        "(" + Globals.Contacts.USERA + " =?pCancelerID " +
                                    " AND " +
                                        Globals.Contacts.USERB + " = ?pExFriendID " + ")" +
                                    " OR ("
                                        + Globals.Contacts.USERB + " =?pCancelerID " +
                                    " AND " +
                                        Globals.Contacts.USERA + " = ?pExFriendID " + ")";

                command.Parameters.Add("@pCancelerID", MySqlDbType.Int32).Value = pCancelerID;
                command.Parameters.Add("@pExFriendID", MySqlDbType.Int32).Value = pExFriendID;

                if (command.ExecuteNonQuery() == 1) _Output = true;


            }
            catch (Exception e)
            {
                _Output = false;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------\n" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {
                command = null;

            }
            return _Output;
        }

        public Boolean CancelFriendship(CustomObjects.User pCanceler, int pExFriendID)
        {
            return this.CancelFriendship(pCanceler.ID, pExFriendID);
        }

        public Boolean CancelFriendship(CustomObjects.User pCanceler, CustomObjects.User pExFriend)
        {
            return this.CancelFriendship(pCanceler.ID, pExFriend.ID);
        }

        public bool UserIsFriend(int pUserID, int pMaybeFriendID)
        {
            Boolean _Output = false;
            MySqlCommand command = null;

            if (pUserID < 1 || pMaybeFriendID < 1) return false;
            try
            {
                command = mConnection.CreateCommand();
                command.CommandText = "SELECT " + Globals.Contacts.ID + " FROM " +
                                        Globals.Tables.CONTACTS +
                                    " WHERE " +
                                        "(" + Globals.Contacts.USERA + " =?pUserID " +
                                    " AND " +
                                        Globals.Contacts.USERB + " = ?pMaybeFriendID " + ")" +
                                    " OR ("
                                        + Globals.Contacts.USERB + " =?pUserID " +
                                    " AND " +
                                        Globals.Contacts.USERA + " = ?pMaybeFriendID " + ")";

                command.Parameters.Add("@pUserID", MySqlDbType.Int32).Value = pUserID;
                command.Parameters.Add("@pMaybeFriendID", MySqlDbType.Int32).Value = pMaybeFriendID;

                int _Count = Convert.ToInt32(command.ExecuteScalar());
                if (_Count > 0) _Output = true;


            }
            catch (Exception e)
            {
                _Output = false;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------\n" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {
                command = null;

            }
            return _Output;
        }

        public bool UserIsFriend(CustomObjects.User pUser, int pMaybeFriendID)
        {
            return this.UserIsFriend(pUser.ID, pMaybeFriendID);
        }
        
        public bool UserIsFriend(CustomObjects.User pUser, CustomObjects.User pMaybeFriend)
        {
            return this.UserIsFriend(pUser.ID, pMaybeFriend.ID);
        }

        public List<User> getUnacceptedFriendRequestForUser(int pUserID)
        {
            List<User> _Users = new List<User>();
            User _User;
            MySqlCommand command = mConnection.CreateCommand();
            MySqlDataReader Reader = null;

            try
            {
                command.CommandText =   "SELECT  " +
                                            Globals.UserFields.ID +" , "  +
                                            Globals.UserFields.NAME +" " +
                                        " FROM " +
                                            Globals.Tables.USER +
                                        " JOIN " +
                                            Globals.Tables.FRIENDREQUESTS +
                                        " ON " +
                                            Globals.UserFields.ID + " = " + Globals.ConRequestFields.REQUESTER +
                                        " WHERE " + 
                                            Globals.UserFields.ID + "<> ?pUserID  LIMIT 0, 1000 ";

                command.Parameters.Add("pUserID", MySqlDbType.Int32).Value = pUserID;
                command.CommandTimeout = 200;
                Reader = command.ExecuteReader();
                if (!Reader.HasRows) return null;

                while (Reader.Read())
                {
                    _User = new User(Convert.ToInt32(Reader[Globals.UserFields.ID]));
                    _User.Name = Reader[Globals.UserFields.NAME].ToString();
                    _Users.Add(_User);
                }

                

            }
            catch (Exception e)
            {
                _User = null;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {
                if (Reader != null)
                    if (!Reader.IsClosed)
                        Reader.Close();
                Reader = null;
                command = null;
            }
            return _Users;
        }

        public List<CustomObjects.User> getUnacceptedFriendRequestForUser(CustomObjects.User pUser)
        {
            return this.getUnacceptedFriendRequestForUser(pUser.ID);
        }

        public List<CustomObjects.User> getFriendListForUser(int pUserID)
        {
          
            List<User> _Users = new List<User>();
            User _User;
            MySqlCommand command = mConnection.CreateCommand();
            MySqlDataReader Reader = null;

            try
            {
                command.CommandText = "SELECT  " +
                                            Globals.UserFields.ID + " , " +
                                            Globals.UserFields.NAME +
                                        " FROM " +
                                            Globals.Tables.USER +
                                        " join " +
                                            Globals.Tables.CONTACTS +
                                        " ON " +
                                            Globals.UserFields.ID + " = " + Globals.Contacts.USERA +
                                        " OR " +
                                            Globals.UserFields.ID + " = " + Globals.Contacts.USERB +
                                        " WHERE " +
                                            Globals.UserFields.ID + " <> ?pUseriD  LIMIT 0, 1000 ";

                command.Parameters.Add("pUseriD", MySqlDbType.Int32).Value = pUserID;
                command.CommandTimeout = 5000;
                Reader = command.ExecuteReader();
                if (!Reader.HasRows) return null;

                while (Reader.Read())
                {
                    _User = new User(Convert.ToInt32(Reader[Globals.UserFields.ID]));
                    _User.Name = Reader[Globals.UserFields.NAME].ToString();
                    _Users.Add(_User);
                }



            }
            catch (Exception e)
            {
                _User = null;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {
                if (Reader != null)
                    if (!Reader.IsClosed)
                        Reader.Close();
                Reader = null;
                command = null;
            }
            return _Users;
        }

        public List<CustomObjects.User> getFriendListForUser(CustomObjects.User pUser)
        {
            return this.getFriendListForUser(pUser.ID);
        }

        public bool UserAllreadyRequested(int pRequesterID, int pTagetUserID)
        {
            Boolean _Output = false;
            MySqlCommand command = null;

            if (pRequesterID < 1 || pTagetUserID < 1) return false;
            try
            {
                command = mConnection.CreateCommand();
                command.CommandText = "SELECT " + Globals.ConRequestFields.ID + " FROM " +
                                        Globals.Tables.FRIENDREQUESTS +
                                    " WHERE " +
                                        Globals.ConRequestFields.REQUESTER + " =?pRequesterID " +
                                    " AND " +
                                        Globals.ConRequestFields.TARGET + " = ?pTagetUserID " +
                                    " AND " +
                                        Globals.ConRequestFields.DECLINED + " = false ";

                command.Parameters.Add("@pRequesterID", MySqlDbType.Int32).Value = pRequesterID;
                command.Parameters.Add("@pTagetUserID", MySqlDbType.Int32).Value = pTagetUserID;
                int _Count = Convert.ToInt32(command.ExecuteScalar());
                if (_Count > 0) _Output = true;


            }
            catch (Exception e)
            {
                _Output = false;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------\n" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {
                command = null;

            }
            return _Output;
        }

        public bool UserAllreadyRequested(CustomObjects.User pRequester, int pTagetUserID)
        {
            return this.UserAllreadyRequested(pRequester.ID, pTagetUserID);
        }

        public bool UserAllreadyRequested(CustomObjects.User pRequester, CustomObjects.User pTagetUser)
        {
            return this.UserAllreadyRequested(pRequester.ID, pTagetUser.ID);
        }

        private bool RemoveRequest(int pRequesterID, int pTargetUserID)
        {
            Boolean _Output = false;
            MySqlCommand command = null;

            if (pTargetUserID < 1 || pRequesterID < 1) return false;
            try
            {
                command = mConnection.CreateCommand();
                command.CommandText = "DELETE FROM " + Globals.Tables.FRIENDREQUESTS +
                                    " WHERE " +
                                        Globals.ConRequestFields.REQUESTER + " =?RequesterID " +
                                        " AND " +
                                        Globals.ConRequestFields.TARGET + " = ?TargetUserID";
                                     

                command.Parameters.Add("@RequesterID", MySqlDbType.Int32).Value = pRequesterID;
                command.Parameters.Add("@TargetUserID", MySqlDbType.Int32).Value = pTargetUserID;

                if (command.ExecuteNonQuery() == 1) _Output = true;
            }
            catch (Exception e)
            {
                _Output = false;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------\n" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {
                command = null;

            }
            return _Output;
        }
    }
}
